/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBHandling;

import Entity.Bill;
import Entity.BillDetail;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author NguyenPhuThien
 */
public class BillHandling {

    private DBUtil objDB;

    public BillHandling() {
        this.objDB = new DBUtil();
    }

    public boolean insertBill(Bill bill) throws Exception {
        try {
            String sql = "{call sp_addBill" + "(?, ?, ?, ?, ?)}";
            Object[] objects = {bill.getBillID(), bill.getUsername(), bill.getDate(), bill.getCustomerName(), bill.getPayment()};
            return objDB.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public boolean deleteBill(String id) throws Exception {
        try {
            String sql = "{call sp_removeBill" + "(?)}";
            Object[] objects = {id};
            return objDB.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public int getTotalBill(String from, String to, String createdBy) throws Exception {
        try {
            String sql= "Select count(*) from Bills";
            if (from != null && createdBy.equals("All Sales Person")) {
                sql += " WHERE date >= '" + from + "' AND date <= '" + to + "'";
            }
            if (!createdBy.equals("All Sales Person")) {
                sql += " WHERE date >= '" + from + "'" + " AND date <= '" + to + "'" + " AND username ='" + createdBy + "'";
            }
            ResultSet rs = objDB.executeSQL(sql);
            rs.next();
            return rs.getInt(1);
        } catch (Exception ex) {
            throw ex;
        } finally {
            objDB.closeConnection();
        }
    }

    public double getTotalMoney(String from, String to, String transactedBy) throws Exception {
        try {
            String sql = "select i.price, bd.quantity from Bills as b, BillDetails as bd, Items as i where b.billID = bd.billID AND i.itemID = bd.itemID";
            if (from != null) {
                sql += " AND b.date >= '" + from + "' AND b.date <= '" + to + "'";
            }
            if (!transactedBy.equals("All Sales Person")) {
                sql += " AND b.username= '" + transactedBy + "'";
            }
            ResultSet rs = objDB.executeSQL(sql);
            double total = 0;
            while (rs.next()) {
                double price = rs.getFloat("price");
                int quantity = rs.getInt("quantity");
                total += (price * quantity);
            }
            return total;
        } catch (Exception ex) {
            throw ex;
        } finally {
            objDB.closeConnection();
        }
    }
    
    public Vector<BillDetail> getBillDetailsByName(String itemName) throws Exception{  
    try{
        String sql="select i.itemName, b.date, sum(quantity) as 'quantity' from Items i, Bills b, BillDetails bd where i.itemID = bd.itemID AND b.billID = bd.billID AND i.itemName ='"+ itemName+"' group by i.itemName, b.date";
        ResultSet rs = objDB.executeSQL(sql);
        Vector<BillDetail> bds = new Vector<BillDetail>();
        while(rs.next()){
            BillDetail bd = new BillDetail();
            bd.setItemName(rs.getString("itemName"));
            bd.setDate(rs.getDate("date"));
            bd.setQuantity(rs.getInt("quantity"));
            bds.add(bd);
        }
        return bds;
    } catch (Exception ex) {
            throw ex;
        } finally {
            objDB.closeConnection();
        }
    }
    
}
